﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;

namespace LogicUniversityBiz
{
    public class CoreClerkFascade
    {
        LogicUniversityEntities contextDB = new LogicUniversityEntities();

        public static EFFascade getEFFascade()
        {
            return new EFFascade();

        }

        //to retrieve all catelogue
        public List<Catelogue> getAllCatelogue()
        {


            var query = from c in contextDB.Catelogues
                        select c;

            return query.ToList();
        }

        public List<String> getAllCategory()
        {
            var query = from c in contextDB.Catelogues
                        select c.Category;

            return query.ToList();
        }

        public List<Requisition> getApprovedRequistionByDate(DateTime approvedDate)
        {


            var query = from c in contextDB.Requisitions
                        orderby c.Approved_Date ascending

                        where c.Approved_Date <= approvedDate && c.Status.Equals("Approved") && c.Process_Status =="Pending"
                        select c;
            return query.ToList();

        }

        public Department getDepartment(string dept_code)
        {
            var query = from c in contextDB.Departments
                        where c.Dept_Code == dept_code
                        select c;
            return query.SingleOrDefault();
        }

        public List<RequisitionCustom> getCurrentRequisition(string dept_Code, DateTime dateBefore)
        {
            dateBefore = new DateTime(dateBefore.Year, dateBefore.Month, dateBefore.Day);
            var query = from j in
                            (from i in
                                 (from c in contextDB.Requisitions
                                  join p in contextDB.Requisition_Detail on c.Requisition_ID equals p.Req_ID into pc
                                  where c.Status == "Approved" & c.Process_Status == "Pending" & c.Approved_Date <= dateBefore
                                  from pp in pc
                                  select new { Department = c.Dept_Code, pp.Quantity, ItemCode = pp.Item_Code })
                             let k = new { ItemCode = i.ItemCode, Department = i.Department }
                             group i by k into g
                             select new { ItemCode = g.Key.ItemCode, Department = g.Key.Department, Qty = g.Sum(i => i.Quantity) })
                        join l in contextDB.Catelogues on j.ItemCode equals l.Item_Code into final
                        from finalRow in final
                        where j.Department == dept_Code
                        select new RequisitionCustom
                        {
                            Item_Code = j.ItemCode,
                            Item_Description = finalRow.Item_Description,
                            Dept_Code = j.Department,
                            Balance = finalRow.Balance,
                            Needed = j.Qty
                        };
            return query.ToList<RequisitionCustom>();
        }
        public List<RequisitionCustom> getOutStanding(string dept_Code)
        {
            var query = from i in contextDB.Retrieval_List
                        join j in contextDB.Catelogues on i.Item_Code equals j.Item_Code into result
                        where i.Dept_Code == dept_Code & i.Outstanding_Status == true & i.Need_Quanty - i.Actual_Quanty != 0
                        from p in result
                        select new RequisitionCustom
                        {
                            Balance = p.Balance,
                            Item_Description = p.Item_Description,
                            Item_Code = i.Item_Code,
                            Needed = i.Need_Quanty - i.Actual_Quanty,
                            Dept_Code = i.Dept_Code
                        };
            return query.ToList<RequisitionCustom>();
        }
        public List<RequisitionCustom> getRequisitionToAdd(string dept_code, DateTime dateBefore)
        {
            dateBefore = new DateTime(dateBefore.Year, dateBefore.Month, dateBefore.Day);
            var currentrequisition = this.getCurrentRequisition(dept_code, dateBefore);
            var outstanding = this.getOutStanding(dept_code);

            var unionResult = currentrequisition.Union(outstanding);

            var query = from i in unionResult
                        group i by new { i.Balance, i.Dept_Code, i.Item_Code, i.Item_Description } into p
                        select new RequisitionCustom
                        {
                            Balance = p.Key.Balance,
                            Dept_Code = p.Key.Dept_Code,
                            Item_Description = p.Key.Item_Description,
                            Item_Code = p.Key.Item_Code,
                            Needed = p.Sum(i => i.Needed)
                        };
            return query.ToList<RequisitionCustom>();
        }

        //working well
        public bool updateAndInsertReterival(string dept_code, DateTime dateBefore, List<RequisitionCustom> reterivalList)
        {
            dateBefore = new DateTime(dateBefore.Year, dateBefore.Month, dateBefore.Day);
            using (TransactionScope scope = new TransactionScope())
            {
                
                try
                {
                    //updating Requisition Status (Process_Status) proceed
                    foreach (var e in contextDB.Requisitions.Where(i => i.Status == "Approved" & i.Process_Status == "Pending" & i.Dept_Code == dept_code & i.Approved_Date <= dateBefore).ToList())
                    {
                        e.Process_Status = "Proceed";
                    }

                    //updating previous Retrieval_Lists status (Outstanding ones)
                    foreach (var e in contextDB.Retrieval_List.Where(i => i.Outstanding_Status == true & i.Dept_Code == dept_code & i.Need_Quanty - i.Actual_Quanty != 0).ToList())
                    {
                        e.Outstanding_Status = false;
                        e.Disbursement_Status = true;
                    }

                    //calculating batch number
                    DateTime issueDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                    var batch_No =contextDB.Retrieval_List.Any()? contextDB.Retrieval_List.Max(x => x.Batch_No):1;
                    var todayOrPrevous = (from i in contextDB.Retrieval_List
                                          where i.Batch_No == batch_No & i.IssueDate == issueDate & i.Disbursement_Status == false
                                          select i.Batch_No).FirstOrDefault();
                    if (batch_No != todayOrPrevous)
                    {
                        batch_No += 1;
                    }

                    //inserting new rows in Retrieval_Lists
                    for (int i = 0; i < reterivalList.Count; i++)
                    {
                        Retrieval_List ret = new Retrieval_List();
                        ret.Disbursement_Status = false;
                        ret.Dept_Code = dept_code;
                        ret.Item_Code = reterivalList[i].Item_Code;
                        ret.Need_Quanty = reterivalList[i].Needed;
                        ret.Actual_Quanty = reterivalList[i].Actual_Quanty;
                        ret.Outstanding_Status = false;
                        ret.IssueDate = issueDate;
                        ret.Batch_No = batch_No;
                        contextDB.Retrieval_List.AddObject(ret);
                    }

                    //updating Catalogues Balance for each items
                    for (int i = 0; i < reterivalList.Count; i++)
                    {
                        string Item_CodeForSearch = reterivalList[i].Item_Code;
                        var e = (from ii in contextDB.Catelogues
                                 where ii.Item_Code == Item_CodeForSearch
                                 select ii).SingleOrDefault();
                        e.Balance = e.Balance - reterivalList[i].Actual_Quanty;
                        //Stock_Card s = new Stock_Card();
                        //s.Item_Code = e.Item_Code;
                        //s.Stock_Card_Detail = reterivalList[i].Dept_Code;
                        //s.

                        var stockCard = (from ii in contextDB.Stock_Card
                                         where ii.Item_Code == Item_CodeForSearch
                                         select ii).FirstOrDefault();
                        if (stockCard == null)
                        {
                            DateTime stockCardDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                            Stock_Card stCard = new Stock_Card();
                            stCard.Item_Code = Item_CodeForSearch;
                            Stock_Card_Detail detail = new Stock_Card_Detail();
                            detail.Date = stockCardDate;
                            // detail.Stock_Card_ID = stockCard.StockCard_ID;
                            detail.Description = reterivalList[i].Dept_Code;
                            detail.Balance = e.Balance;
                            detail.Quantity = reterivalList[i].Actual_Quanty;
                            stCard.Stock_Card_Detail.Add(detail);
                            contextDB.Stock_Card.AddObject(stCard);
                        }
                        else
                        {
                            DateTime stockCardDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                            Stock_Card_Detail detail = new Stock_Card_Detail();
                            detail.Date = stockCardDate;
                            detail.Stock_Card_ID = stockCard.StockCard_ID;
                            detail.Description = reterivalList[i].Dept_Code;
                            detail.Balance = e.Balance;
                            detail.Quantity = reterivalList[i].Actual_Quanty;
                            contextDB.Stock_Card_Detail.AddObject(detail);
                        }
                    }
                    contextDB.SaveChanges();
                    scope.Complete();
                    return true;
                }
                
                catch (Exception e)
                {
                    return false;
                }
            }
        }

        public List<CompositeReterivalData> getRetrievalList()
        {
            try
            {
                var batch_No = contextDB.Retrieval_List.Max(x => x.Batch_No);
                var query = from i in contextDB.Retrieval_List
                            where i.Disbursement_Status == false //& i.Batch_No == batch_No
                            //group i by new { i.Item_Code, i.Batch_No, i.Catelogue } into gcs
                            group i by new { i.Item_Code, i.Catelogue } into gcs
                            select new CompositeReterivalData
                            {
                                Item_Code = gcs.Key.Item_Code,
                                //Batch_No = gcs.Key.Batch_No,
                                Item_Description = gcs.Key.Catelogue.Item_Description,
                                RequestedQty = gcs.Sum(e => e.Need_Quanty),
                                RetrievedQty = gcs.Sum(e => e.Actual_Quanty)
                            };
                return query.ToList<CompositeReterivalData>();
            }
            catch (InvalidOperationException e)
            {
                return null;
            }
        }
        public List<RetrievalDetailData> getRetrievalDetail(string item_Code, int batch_No)
        {
            var query = from i in contextDB.Retrieval_List
                        where i.Item_Code == item_Code & i.Disbursement_Status==false //& i.Batch_No == batch_No
                        select new RetrievalDetailData
                        {
                            Id = i.ID,
                            Dept_Code = i.Dept_Code,
                            Dept_Name = i.Department.Dept_Name,
                            Needed = i.Need_Quanty,
                            Actual = i.Actual_Quanty
                        };
            return query.ToList<RetrievalDetailData>();

        }
        //working well
        public bool updateRetrievalDetail(List<RetrievalDetailData> result, int adjustedAmount)
        {
            string Item_CodeForSearch = "";
            using (TransactionScope scope = new TransactionScope())
            {
                try
                {

                    foreach (RetrievalDetailData r in result)
                    {
                        var ew = (from ii in contextDB.Catelogues
                                 where ii.Item_Code == Item_CodeForSearch
                                 select ii).SingleOrDefault();

                        if (ew.Balance-adjustedAmount < 0)
                        {
                            throw new Exception();
                        }
                        var retrievalDetail = (from i in contextDB.Retrieval_List
                                               where i.ID == r.Id
                                               select i).Single();
                        retrievalDetail.Actual_Quanty = r.Actual;
                        Item_CodeForSearch = retrievalDetail.Item_Code;
                    }
                    var e = (from ii in contextDB.Catelogues
                             where ii.Item_Code == Item_CodeForSearch
                             select ii).SingleOrDefault();
                    
                    var stockCard = (from ii in contextDB.Stock_Card
                                     where ii.Item_Code == Item_CodeForSearch
                                     select ii).SingleOrDefault();
                    if (stockCard == null)
                    {
                        DateTime stockCardDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                        Stock_Card stCard = new Stock_Card();
                        stCard.Item_Code = Item_CodeForSearch;
                        Stock_Card_Detail detail = new Stock_Card_Detail();
                        detail.Date = stockCardDate;
                        // detail.Stock_Card_ID = stockCard.StockCard_ID;
                        detail.Description = "Adjust when retrieving Data";
                        detail.Balance = e.Balance;
                        detail.Quantity = adjustedAmount;
                        stCard.Stock_Card_Detail.Add(detail);
                        contextDB.Stock_Card.AddObject(stCard);
                    }
                    else
                    {
                        DateTime stockCardDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                        Stock_Card_Detail detail = new Stock_Card_Detail();
                        detail.Date = stockCardDate;
                        detail.Stock_Card_ID = stockCard.StockCard_ID;
                        detail.Description = "Adjust when retrieving Data";
                        detail.Balance = e.Balance;
                        detail.Quantity = adjustedAmount;
                        contextDB.Stock_Card_Detail.AddObject(detail);
                    }
                    contextDB.SaveChanges();
                    scope.Complete();
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }
        public List<DisbursementData> getDisbursementList()
        {
            try
            {
                var batch_No = contextDB.Retrieval_List.Max(x => x.Batch_No);
                var query = (from i in contextDB.Retrieval_List
                             where i.Disbursement_Status==false //i.Batch_No == batch_No
                             select new DisbursementData
                             {
                                 Dept_No = i.Department.Dept_Code,
                                 Dept_Name = i.Department.Dept_Name,
                                 Collection_Point = i.Department.Collection_Point,
                                 Representative_Name = i.Department.Rep_Name,
                                 Disbursement_Status = i.Disbursement_Status
                             }).Distinct();
                return query.ToList<DisbursementData>();
            }
            catch (InvalidOperationException e)
            {
                return null;
            }
        }
        public List<Retrieval_List> getDisbursementDetail(int batch_No, string dept_Code)
        {
            var query = from i in contextDB.Retrieval_List
                        where i.Disbursement_Status==false & i.Dept_Code==dept_Code//i.Batch_No == batch_No & i.Dept_Code == dept_Code
                        select i;
            return query.ToList();
        }
        public void addAndUpdateAdjust(Voucher_Detail v, int batch_No, string dept_Code, string employee_ID)
        {

            using (TransactionScope scope = new TransactionScope())
            {
                DateTime issueDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                Adjustment_Voucher a = new Adjustment_Voucher();
                a.IssuedDate = issueDate;
                a.Voucher_Detail.Add(v);
                a.Conducted_By = employee_ID;
               //S a.Approved_By = employee_ID;
                contextDB.Adjustment_Voucher.AddObject(a);
                contextDB.SaveChanges();

                var item = contextDB.Catelogues.Where(x => x.Item_Code == v.Item_Code).Select(x => x).Single();
                item.Balance = item.Balance + v.Quantity_Adjusted;
                contextDB.SaveChanges();

                var retrieval_List = (from i in contextDB.Retrieval_List
                                      where i.Dept_Code == dept_Code & i.Item_Code==v.Item_Code//i.Batch_No == batch_No & i.Item_Code == v.Item_Code
                                      select i).Single();
                retrieval_List.Actual_Quanty = retrieval_List.Actual_Quanty - v.Quantity_Adjusted;
                contextDB.SaveChanges();

                scope.Complete();

            }
        }
        public bool updateDeptDisbusement(List<Retrieval_List> retrieval_List, string employee_ID)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                try
                {

                    foreach (Retrieval_List rel in retrieval_List)
                    {
                        if (rel.Need_Quanty - rel.Actual_Quanty != 0)
                        {
                            rel.Outstanding_Status = true;
                        }
                        rel.Disbursement_Status = true;
                        rel.Dursebed_By = employee_ID;
                    }
                    contextDB.SaveChanges();
                    scope.Complete();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }
        public void unDisburseRetrieval(DisbursementData data)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                var query = (from i in contextDB.Retrieval_List
                             where data.Batch_No == i.Batch_No & data.Dept_No == i.Dept_Code
                             select i).ToList();
                foreach (Retrieval_List rel in query)
                {
                    //adding into adjustment
                    DateTime issueDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                    Adjustment_Voucher a = new Adjustment_Voucher();
                    a.IssuedDate = issueDate;
                    Voucher_Detail v = new Voucher_Detail();
                    v.Quantity_Adjusted = rel.Actual_Quanty;
                    v.Reason = "return because not come to collection point";
                    v.Item_Code = rel.Item_Code;
                    a.Voucher_Detail.Add(v);
                    contextDB.Adjustment_Voucher.AddObject(a);

                    string Item_CodeString = rel.Item_Code;
                    //updating Catelogue balance
                    var item = contextDB.Catelogues.Where(x => x.Item_Code == Item_CodeString).Single();
                    item.Balance += rel.Actual_Quanty;

                    //updating stock_Card
                    var stockCard = (from ii in contextDB.Stock_Card
                                     where ii.Item_Code == Item_CodeString
                                     select ii).Single();
                    if (stockCard == null)
                    {
                        DateTime stockCardDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                        Stock_Card stCard = new Stock_Card();
                        stCard.Item_Code = Item_CodeString;
                        Stock_Card_Detail detail = new Stock_Card_Detail();
                        detail.Date = stockCardDate;
                        //detail.Stock_Card_ID = stockCard.StockCard_ID;
                        detail.Description = rel.Dept_Code + " returning";
                        detail.Balance = item.Balance;
                        detail.Quantity = rel.Actual_Quanty;
                        stCard.Stock_Card_Detail.Add(detail);
                        contextDB.Stock_Card.AddObject(stCard);
                    }
                    else
                    {
                        DateTime stockCardDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                        Stock_Card_Detail detail = new Stock_Card_Detail();
                        detail.Date = stockCardDate;
                        detail.Stock_Card_ID = stockCard.StockCard_ID;
                        detail.Description = rel.Dept_Code + " returning";
                        detail.Balance = item.Balance;
                        detail.Quantity = rel.Actual_Quanty;
                        contextDB.Stock_Card_Detail.AddObject(detail);
                    }

                    //updating retrieval_List
                    rel.Outstanding_Status = true;
                    rel.Disbursement_Status = true;
                    rel.Actual_Quanty = 0;

                }
            }
        }

        public List<Department> getAllDepartment()
        {
            var query = from i in contextDB.Departments
                        select i;
            return query.ToList<Department>();
        }
        public Employee getEmployeeInfo(string employeeID)
        {
            var query = from i in contextDB.Employees
                        where i.Employee_ID == employeeID
                        select i;
            return query.SingleOrDefault();
        }
        public IQueryable getDeptMonthlyRequest(int firstMonth, int secondMonth, int thirdMonth, string dept_Code)
        {
            var query = from a in
                            (
                                from i in contextDB.Requisitions
                                join p in contextDB.Requisition_Detail on i.Requisition_ID equals p.Req_ID
                                join j in contextDB.Catelogues on p.Item_Code equals j.Item_Code into results
                                where (i.Requested_Date.Month == firstMonth || i.Requested_Date.Month == secondMonth || i.Requested_Date.Month == thirdMonth) & i.Dept_Code == dept_Code
                                from pp in results
                                select new { Category = pp.Category, Month = i.Requested_Date.Month, Quantity = p.Quantity })
                        let k = new { Category = a.Category, Month = a.Month }
                        group a by k into g
                        select new { Category = g.Key.Category, Month = g.Key.Month, Quantity = g.Sum(a => a.Quantity) };
            return query;
        }
        public IQueryable getOrderMonthlyReport(int firstMonth, int secondMonth, int thirdMonth)
        {
            var query = from a in
                            (
                                from i in contextDB.Purchse_Order
                                join p in contextDB.Purchase_Order_Detail on i.PO_No equals p.PO_No
                                join j in contextDB.Catelogues on p.Item_Code equals j.Item_Code into results
                                where i.Order_Datee.Month == firstMonth || i.Order_Datee.Month == secondMonth || i.Order_Datee.Month == thirdMonth
                                from pp in results
                                select new { Category = pp.Category, Month = i.Order_Datee.Month, Quantity = p.Quantity })
                        let k = new { Category = a.Category, Month = a.Month }
                        group a by k into g
                        select new { Category = g.Key.Category, Month = g.Key.Month, Quantity = g.Sum(a => a.Quantity) };
            return query;
        }

        public List<Catelogue> getCatelogueItemsUnderReorderLevel()
        {
            var query = (from c in contextDB.Catelogues
                         where c.Balance <= c.Reorder_Level
                         select c).OrderBy(x=>x.Balance);

            return query.ToList();
        }

        public List<string> getAllItemCode()
        {
            var query = from c in contextDB.Catelogues
                        select c.Item_Code;

            return query.ToList();
        }

        public List<Stock_Card_Detail> getStockInfoOf(string itemCode) {

            var query = from stock in contextDB.Stock_Card
                        join sd in contextDB.Stock_Card_Detail
                        on stock.StockCard_ID equals sd.Stock_Card_ID
                        where stock.Item_Code == itemCode
                        select sd;

            return query.ToList();
        }

        public List<Stock_Card_Detail> getStockInfoOf(string itemCode, DateTime date)
        {
            var query = from stock in contextDB.Stock_Card
                        join sd in contextDB.Stock_Card_Detail
                        on stock.StockCard_ID equals sd.Stock_Card_ID
                        where stock.Item_Code == itemCode && sd.Date==date
                        select sd;

            return query.ToList();
        }

        public Catelogue getItemInfoOf(string itemCode)
        {
            var query = from c in contextDB.Catelogues
                        where c.Item_Code == itemCode
                        select c;

            return query.SingleOrDefault();
        }
    }
}
